if exists (select 1 from sysobjects where name = 'get_personjpcolumn' and type = 'P')
begin
   drop procedure get_personjpcolumn
   print 'Procedure: get_personjpcolumn deleted ...'
end
go
create procedure get_personjpcolumn(
  @prsid             int = 3,
  @abteilungid       int = 1,
  @planid            int = 27,
  @dienstids         varchar(500) = '',
  @datumer           varchar(4084) = '',--20081101
  @selectall         bit = 1
)
as
begin
  set nocount on

   declare @counter int
   declare @lendienstids int
   declare @lendatum int
   
   create table #datum
   (
      datum           datetime
   )
   
   create table #dienste
   (
      dienstid       int
   )
   
   create table #plan
   (
      datum          datetime,
      dienstname     varchar(32),
      dienstid       int,
      colorname      varchar(32),
      rgb            int,
      apname         varchar(32),
      monatname      varchar(32),
      soll           decimal(5,2),
      haben          decimal(5,2),
      ferien         decimal(5,2),
      anpassung      decimal(5,2)
   )
   
   select @lendienstids = LEN(@dienstids)
   select @lendatum = LEN(@datumer)
   
   select @counter = 1
   while @counter < @lendienstids
   begin
      insert into #dienste (dienstid) values (CONVERT(int,SUBSTRING(@dienstids,@counter,2)))
      select @counter = @counter + 2
   end
   
   
   select @counter = 1
   while @counter < @lendatum
   begin
      insert into #datum (datum) values (convert(datetime,SUBSTRING(@datumer,@counter,8),112))
      select @counter = @counter + 8
   end

   
   insert #plan
   select datum        = dp.Datum,
          dienstname   = d.Name,
          dienstid     = d.DienstID,
          colorname    = d.ColorName,
          rgb          = d.RGB,
          apname       = ap.Name,
          monatname    = at.Name,
          soll         = isnull(az.Soll,0),
          haben        = isnull(az.Haben,0),
          ferien       = isnull(az.Ferien,0),
          anpassung    = 0
     from PlanSchema ps
     left outer join ArbeitsPlan ap
       on ap.SchemaID = ps.SchemaID
    inner join Dienstplan dp
       on dp.PlanID = ap.PlanID
      and dp.PrsID = ps.PrsID
      and dp.AbteilungID = ps.AbteilungID
     left outer join Dienst d
       on dp.DienstID = d.DienstID
     left outer join ArbeitsTage at
       on at.ATID = ap.ATID
     left outer join Arbeitszeit az
       on az.PlanID = ap.PlanID
      and az.PrsID = ps.PrsID
      and az.AbteilungID = ps.AbteilungID
    where dp.AbteilungID = @abteilungid
      and ap.PlanID = @planid
      and ps.PrsID = @prsid
      and (@selectall = 1
           or(dp.Datum in (select datum from #datum) 
            and @selectall = 0))
    order by dp.Datum

if @lendienstids > 0
  begin
   insert #plan
   select datum        = dp.Datum,
          dienstname   = d.Name,
          dienstid     = d.DienstID,
          colorname    = d.ColorName,
          rgb          = d.RGB,
          apname       = ap.Name,
          monatname    = at.Name,
          soll         = isnull(az.Soll,0),
          haben        = isnull(az.Haben,0),
          ferien       = isnull(az.Ferien,0),
          anpassung    = 0
     from PlanSchema ps
     left outer join ArbeitsPlan ap
       on ap.SchemaID = ps.SchemaID
    inner join Dienstplan dp
       on dp.PlanID = ap.PlanID
      and dp.PrsID = ps.PrsID
      and dp.AbteilungID = ps.AbteilungID
     left outer join Dienst d
       on dp.DienstID = d.DienstID
     left outer join ArbeitsTage at
       on at.ATID = ap.ATID
     left outer join Arbeitszeit az
       on az.PlanID = ap.PlanID
      and az.PrsID = ps.PrsID
      and az.AbteilungID = ps.AbteilungID
    where dp.AbteilungID = @abteilungid
      and ap.PlanID = @planid
      and ps.PrsID = @prsid
      and d.DienstID in (select dienstid from #dienste)
    order by dp.Datum
  end
    
   update #plan
      set colorname = cp.Colorname,
          rgb       = cp.ARGB
     from Colorplan cp
    where cp.Datum = #plan.datum
      and cp.AbteilungID = @abteilungid
      and cp.PlanID = @planid
      and cp.PrsID = @prsid

   insert #plan
   select datum        = cp.Datum,
          dienstname   = '',
          dienstid     = 0,
          colorname    = cp.Colorname,
          rgb          = cp.ARGB,
          apname       = ap.Name,
          monatname    = at.Name,
          soll         = isnull(az.Soll,0),
          haben        = isnull(az.Haben,0),
          ferien       = isnull(az.Ferien,0),
          anpassung    = 0
     from PlanSchema ps
     left outer join ArbeitsPlan ap
       on ap.SchemaID = ps.SchemaID
    inner join Colorplan cp
       on cp.PlanID = ap.PlanID
      and cp.PrsID = ps.PrsID
      and cp.AbteilungID = ps.AbteilungID
     left outer join ArbeitsTage at
       on at.ATID = ap.ATID
     left outer join Arbeitszeit az
       on az.PlanID = ap.PlanID
      and az.PrsID = ps.PrsID
      and az.AbteilungID = ps.AbteilungID
    where ps.AbteilungID = @abteilungid
      and ap.PlanID = @planid
      and ps.PrsID = @prsid
      and cp.Datum not in (select datum from #plan)

 declare @anpassung decimal(5,2)
  select @anpassung = isnull(sum(sa.Stunden),0)
    from Saldoanpassung sa
    left outer join ArbeitsPlan ap
      on ap.PlanID = sa.PlanID
   where sa.PrsID = @prsid
     and ap.PlanID = @planid
     and sa.AbteilungID = @abteilungid

  update #plan
     set anpassung = @anpassung

   select datum,
          dienstname,
          dienstid,
          colorname,
          rgb,
          apname,
          monatname,
          soll,
          haben,
          ferien,
          anpassung
     from #plan

end
go
print 'Procedure: get_personjpcolumn done ...'
go

grant exec on get_personjpcolumn to prsadmins with grant option
go
grant exec on get_personjpcolumn to prsusers
go

